package com.cma.generator.db;

import com.cma.common.spring.ApplicationContextHolder;
import com.cma.generator.bean.ColumnInfo;
import com.cma.generator.bean.TableInfo;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by bain.wang on 2016/1/11.
 */
public class MySQLGenerator {

    public static TableInfo getTableInfo(String tableName) {
        TableInfo tableInfo = new TableInfo();
        tableInfo.setName(tableName);

        DataSource dataSource = ApplicationContextHolder.getApplicationContext().getBean("dataSourceForGenerator", DataSource.class);

        List<ColumnInfo> columnInfoList = new ArrayList<ColumnInfo>();
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet rst = null;

        String comment = "";
        try {
            connection = dataSource.getConnection();
            pst = connection.prepareStatement("select TABLE_COMMENT  from tables where table_name =  '" + tableName + "' ");//准备执行语句
            rst = pst.executeQuery();

            while (rst.next()) {
                comment = rst.getString(1); // TABLE_COMMENT
            }

            tableInfo.setColumnInfoList(columnInfoList);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rst != null) {
                try {
                    rst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        tableInfo.setComment(comment);

        return tableInfo;
    }

    public static TableInfo getColumnList(TableInfo tableInfo) {
        String tableName = tableInfo.getName();

        DataSource dataSource = ApplicationContextHolder.getApplicationContext().getBean("dataSourceForGenerator", DataSource.class);

        List<ColumnInfo> columnInfoList = new ArrayList<ColumnInfo>();
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet rst = null;

        try {
            connection = dataSource.getConnection();
            pst = connection.prepareStatement("select COLUMN_NAME, COLUMN_TYPE,IS_NULLABLE,COLUMN_KEY,COLUMN_DEFAULT,EXTRA, COLUMN_COMMENT from columns where table_name =  '" + tableName + "' ");//准备执行语句
            rst = pst.executeQuery();

            while (rst.next()) {
                String field = rst.getString(1); // COLUMN_NAME
                String type = rst.getString(2); // COLUMN_TYPE
                String nullset = rst.getString(3); // IS_NULLABLE
                String keyset = rst.getString(4); // COLUMN_KEY
                String defaultSet = rst.getString(5); // COLUMN_DEFAULT
                String extra = rst.getString(6); //EXTRA
                String comment = rst.getString(7); // COLUMN_COMMENT

                ColumnInfo columnInfo = new ColumnInfo();
                columnInfo.setField(field);
                columnInfo.setType(type);
                columnInfo.setNullSet(nullset);
                columnInfo.setKeySet(keyset);
                columnInfo.setDefaultSet(defaultSet);
                columnInfo.setExtra(extra);
                columnInfo.setComment(comment);


                columnInfoList.add(columnInfo);
            }

            tableInfo.setColumnInfoList(columnInfoList);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rst != null) {
                try {
                    rst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }


        return tableInfo;
    }


    public static TableInfo getTableInfo2(String tableName) {
        TableInfo tableInfo = new TableInfo();
        tableInfo.setName(tableName);

        DataSource dataSource = ApplicationContextHolder.getApplicationContext().getBean("dataSourceForGenerator", DataSource.class);

        List<ColumnInfo> columnInfoList = new ArrayList<ColumnInfo>();
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet rst = null;

        try {
            connection = dataSource.getConnection();
            pst = connection.prepareStatement("desc " + tableName);//准备执行语句
            rst = pst.executeQuery();

            while (rst.next()) {
                String field = rst.getString(1);
                String type = rst.getString(2);
                String nullset = rst.getString(3);
                String keyset = rst.getString(4);
                String defaultSet = rst.getString(5);
                String extra = rst.getString(6);

                ColumnInfo columnInfo = new ColumnInfo();
                columnInfo.setField(field);
                columnInfo.setType(type);
                columnInfo.setNullSet(nullset);
                columnInfo.setKeySet(keyset);
                columnInfo.setDefaultSet(defaultSet);
                columnInfo.setExtra(extra);


                columnInfoList.add(columnInfo);
            }

            tableInfo.setColumnInfoList(columnInfoList);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rst != null) {
                try {
                    rst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }


        return tableInfo;
    }


}
